Option Explicit

' 在第一次执行 `getWb` 或 `getWbSilence` 方法时，用来记录已经打开的 workbook 对象的路径
Private openedWbPathsBeforeExec As ISet
' 在第一次执行 `getWb` 或 `getWbSilence` 方法时，用来记录已经打开的 workbook 对象的文件名
Private openedWbNameBeforeExec As ISet

'################################################################
' 有记录的获取、打开、关闭 workbook 对象
'################################################################
' 从已经打开的Excel和路径中搜索指定的Excel，并返回 Workbook，
' @param path 需要搜索的Excel的路径，或文件名
' @return Workbook
Public Function getWb(ByRef path As String) As Workbook
On Error GoTo catch
    If openedWbPathsBeforeExec Is Nothing Then
        fillWbInfoBeforeExec
    End If

    Set getWb = UtilsWorkbook.getOpenedWb(path)

    If getWb Is Nothing Then
        If UtilsIO.pathExist(path) = True Then
            If getWb Is Nothing Then
                Set getWb = UtilsWorkbook.getWbByPath(path)
                If getWb Is Nothing Then
                    GoTo catch
                End If
            End If
        Else
            GoTo catch
        End If
    End If

    Exit Function
catch:
    Set getWb = Nothing
    Err.Raise vbObjectError + 1, "error", "can not get: " & path
End Function

' 从已经打开的Excel和路径中搜索指定的Excel，并返回 Workbook
' 如果指定的 Excel 没有打开，则使用静默的方式打开
' @param path 需要搜索的Excel的路径，或文件名
' @return Workbook
Public Function getWbSilence(ByRef path As String) As Workbook
On Error GoTo catch
    If openedWbPathsBeforeExec Is Nothing Then
        fillWbInfoBeforeExec
    End If

    Set getWb = UtilsWorkbook.getOpenedWb(path)

    If getWbSilence Is Nothing Then
        If UtilsIO.pathExist(path) = True Then
            If getWbSilence Is Nothing Then
                Set getWbSilence = UtilsWorkbook.getWbSilenceByPath(path)
                If getWbSilence Is Nothing Then
                    GoTo catch
                End If
            End If
        Else
            GoTo catch
        End If
    End If

    Exit Function
catch:
    Set getWbSilence = Nothing
    Err.Raise vbObjectError + 1, "error", "can not get: " & path
End Function

' 记录在第一次执行 `getWb` 或 `getWbSilence` 方法时，已经打开的 workbook 对象的路径
Private Sub fillWbInfoBeforeExec()
    Dim app As Object
    Dim book As Workbook

    Set app = GetObject(, "Excel.Application")
    Set openedWbPathsBeforeExec = UtilsSet.createHashSet
    Set openedWbNameBeforeExec = UtilsSet.createHashSet

    For Each book In app.Workbooks
        openedWbPathsBeforeExec.Add(book.FullName)
        openedWbNameBeforeExec.Add(book.Name)
    Next
End Sub

' 关闭 `wb` 文件对象
' 如果参数 `wb` 中的路径在辅助属性 `openedWbPathsBeforeExec` 中，则不会关闭
' @param wb Workbook 对象
Public Sub releaseWb(ByRef wb As Workbook)
    If wb Is Nothing Then
        Exit Sub
    End If

    ' 如果 wb 的路径在 openedWbPathsBeforeExec 中存在，则退出，不需要关闭
    If Not openedWbPathsBeforeExec Is Nothing Then
        If openedWbPathsBeforeExec.Exists(wb.FullName) Then
            Exit Sub
        End If
    End If

    wb.Close
End Sub

' 获取 `wbPath` 路径指向的已经打开的 wb 对象，并关闭
' 如果参数 `wbPath` 在辅助属性 `openedWbPathsBeforeExec` 或 `openedWbNameBeforeExec` 中，则不会关闭
'
' @param wbPath, 需要搜索的Excel的路径或文件名
Public Sub releaseWbByPath(ByRef wbPath As String)
    ' 如果 wb 的路径在 openedWbPathsBeforeExec, openedWbNameBeforeExec 中存在，则退出，不需要关闭
    If Not openedWbPathsBeforeExec Is Nothing Then
        If openedWbPathsBeforeExec.Exists(wbPath) Then
            Exit Sub
        End If
        If openedWbNameBeforeExec.Exists(wbPath) Then
            Exit Sub
        End If
    End If

    closeWbByPath(wbPath)
End Sub

'################################################################
' 其他方法
'################################################################

' 检查指定 excel 是否已经打开
' @param path 文件名或路径
' @return Boolean
Public Function isOpened(ByRef path As String) As Boolean
On Error GoTo catch
    Dim wb As Workbook
    Set wb = UtilsWorkbook.getOpenedWb(path)
    If wb Is Nothing Then
        isOpened = False
    Else
        isOpened = True
    End If

    Set wb = Nothing
    Exit Function
catch:
    ' 如果发生错误，则默认返回 false
    Set wb = Nothing
    isOpened = False
End Function

' 从已经打开的Excel中按照全路径名，或文件名搜索指定的Excel，并返回对应的Workbook对象
' @param path Excel的全路径名或文件名
' @return Workbook
Public Function getOpenedWb(ByRef path As String) As Workbook
On Error GoTo catch
    ' 先检查当前的活动 excel
    If ActiveWorkbook.FullName = path Or ActiveWorkbook.Name = path Then
        Set getOpenedWb = ActiveWorkbook
        Exit Function
    End If

    Dim app As Object
    Dim book As Workbook

    ' Set app = GetObject(, "Excel.Application")
    For Each book In Application.Workbooks
        If book.FullName = path Or book.Name = path Then
            Set getOpenedWb = book
            Exit For
        End If
    Next

    Set app = Nothing
    Set book = Nothing

    Exit Function
catch:
    Set book = Nothing
    Set app = Nothing

    Err.Raise vbObjectError + 1, "error", path & "is not open"
End Function

' 按照指定路径打开指定的 excel 文件，并返回 Workbook
' @param path 需要打开的Excel的路径
' @return Workbook
Public Function getWbByPath(ByRef path As String) As Workbook
On Error GoTo catch
    If UtilsIO.pathExist(path) = True Then
        ' 关闭更新提示
        Set getWbByPath = Workbooks.Open(path, UpdateLinks:=0)
    Else
        Set getWbByPath = Nothing
    End If
    Exit Function
catch:
    Set getWbByPath = Nothing
    Err.Raise vbObjectError + 1, "error", "can not open: " & path
End Function

' 按照指定路径打开指定的 excel 文件，并返回 Workbook
' 使用静默方式打开
' @param path 需要打开的Excel的路径
' @return Workbook
Public Function getWbSilenceByPath(ByRef path As String) As Workbook
On Error GoTo catch
    If UtilsIO.pathExist(path) = True Then
        ' 两种方式都可以 TODO
        ' Set getWbSilenceByPath = CreateObject(path)
        Set getWbSilenceByPath = GetObject(path)
    Else
        Set getWbSilenceByPath = Nothing
    End If
    Exit Function
catch:
    Set getWbSilenceByPath = Nothing
    Err.Raise vbObjectError + 1, "error", "can not open: " & path
End Function

' 将 Workbook 设置为激活状态
' @param wb Workbook
Public Sub activateWb(ByRef wb As Workbook)
    If Not wb Is ActiveWorkbook Then
        wb.Activate
    End If
End Sub

' 如果指定路径对应的 excel 已经被打开，则关闭
' @param path，文件名或路径
Public Sub closeWbByPath(ByRef path As String)
On Error GoTo catch
    Dim wb As Workbook
    Set wb = getOpenedWb(path)
    If Not wb Is Nothing Then
        wb.Close
    End If

    Set wb = Nothing
catch:
    Set wb = Nothing
End Sub

' Workbook 中是否包含指定的 sheetname
' @param wb Workbook
' @param sheetname 待检查的sheet名
' @return Boolean
Public Function hasSheet(ByRef wb As Workbook, ByRef sheetname As String) As Boolean
    Dim s As Variant

    hasSheet = False
    For Each s In wb.Sheets
        If s.Name = sheetname Then
            hasSheet = True
            Exit For
        End If
    Next
End Function

' 尝试在末尾添加一个 sheet。如果目标 sheet 已经存在，则将这个 sheet 移动到最后，并清空所有数据
' @param wb Workbook
' @param sheetname sheet名
' @return 新创建的 Worksheet 对象
Public Function addLastSheet(ByRef wb As Workbook, ByRef sheetname As String) As Worksheet
    If hasSheet(wb, sheetname) = False Then
        wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.count)).Name = sheetname
    Else
        With wb.Sheets(sheetname)
            .Move after:=wb.Sheets(wb.Sheets.count)
            .Cells.Clear
        End With
    End If

    Set addLastSheet = wb.Sheets(sheetname)
End Function

' 尝试在末尾添加一个 sheet，sheet的格式为文字列。如果目标 sheet 已经存在，则将这个 sheet 移动到最后，并清空所有数据
' @param wb Workbook
' @param sheetname sheet名
' @return Worksheet
Public Function addLastTextSheet(ByRef wb As Workbook, ByRef sheetname As String) As Worksheet
    Set addLastTextSheet = addLastSheet(wb, sheetname)
    UtilsWorksheet.setAllCellAsTextByName wb, sheetname
End Function

' 删除指定sheet
' @param wb Workbook
' @param sheetname String
Public Sub deleteSheet(ByRef wb As Workbook, ByRef sheetname As String)
On Error GoTo catch
    ' If UtilsWorkbook.hasSheet(wb, sheetname) = False Then
    If hasSheet(wb, sheetname) = False Then
        Exit Sub
    End If

    Dim originAppDisplayAlerts As Boolean
    originAppDisplayAlerts = Application.DisplayAlerts

    If Application.DisplayAlerts <> False Then
        Application.DisplayAlerts = False
    End If

    wb.Sheets(sheetname).Delete

catch:
    If Application.DisplayAlerts <> originAppDisplayAlerts Then
        Application.DisplayAlerts = originAppDisplayAlerts
    End If
End Sub

' 激活指定的 book 及其下的 sheet 对象
Public Sub activateBookAndSheetObject(ByRef wb As Workbook, ByRef sh As Worksheet)
    wb.Activate
    sh.Activate
End Sub

' 从 Workbook 中获取指定名称的 sheet，并将其清空和设置格式为文字。如果 sheet 不存在，则会在 Workbook 的末尾添加一个格式为文字的 sheet
'
' @param wb Workbook
' @param sheetname 需要获取的 sheet 的 sheet 名
' @return Worksheet
Public Function tryGetEmptyTextSheet(ByRef wb As Workbook, ByRef sheetname As String) As Worksheet
    If UtilsWorkbook.hasSheet(wb, sheetname) Then
        ' 获取指定的配置 sheet 对象
        ' 如果 sheet 已经存在，则清空所有内容，并全部设置成字符串格式
        Set tryGetEmptyTextSheet = wb.Sheets(sheetname)
        UtilsWorksheet.clearAllAndToText tryGetEmptyTextSheet
    Else
        ' 如果这个 sheet 不存在，则进行创建
        Set tryGetEmptyTextSheet = UtilsWorkbook.addLastTextSheet(wb, sheetname)
    End If
End Function